﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Data;
using masterview.Code.models;

namespace masterview.Code.db
{
    public class Database : DatabaseHelper
    {
        #region Public Methods

        #region Public Methods - Local Database

        /// <summary>
        /// 
        /// Note: unused now
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public String GetServiceUrlsById(String Id)
        {
            var sql = new StringBuilder();
            sql.Append("SELECT SII_URL ");
            sql.Append("FROM SERVICE_INSTANCE_INFORMATION ");
            sql.Append("WHERE INSTALL_ID = @id");

            try
            {
                SetLocalDatabaseConnection();
                var cmd = new SqlCommand(sql.ToString(), con);
                cmd.Parameters.Add("@id", SqlDbType.VarChar, Id.Length).Value = Id;
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                String result = null;
                while (reader.Read())
                {
                    result = (string)reader["CS_FULL_STRING"] + "<br/>";
                }
                if (reader.HasRows)
                {
                    if (result != null) result = result.Substring(0, result.Length - result.LastIndexOf("<br/>"));
                }

                reader.Close();
                cmd.Parameters.Clear();
                return result;
            }
            catch (SqlException e)
            {
                throw new ApplicationException("Data error.");
            }
            finally
            {
                con.Close();
            }
        }

        public String GetConnectionStringById(String Id)
        {
            String result = null;
            var sql = new StringBuilder();
            sql.Append("SELECT CS_FULL_STRING ");
            sql.Append("FROM CONNECTION_STRING ");
            sql.Append("WHERE CS_ID = @id");

            try
            {
                SetLocalDatabaseConnection();
                var cmd = new SqlCommand(sql.ToString(), con);
                cmd.Parameters.Add("@id", SqlDbType.VarChar, Id.Length).Value = Id;
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = (string)reader["CS_FULL_STRING"];
                }
                reader.Close();
                cmd.Parameters.Clear();
                return result;
            }
            catch (SqlException e)
            {
                throw new ApplicationException("Data error.");
            }
            finally
            {
                con.Close();
            }
        }

        public String GetListOfLocalServiceIds()
        {
            String result = null;
            var sql = new StringBuilder();
            sql.Append("SELECT SI_ID ");
            sql.Append("FROM SERVICE_INSTANCE_INFORMATION ");

            try
            {
                SetLocalDatabaseConnection();
                var cmd = new SqlCommand(sql.ToString(), con);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    result = (string)reader["SI_ID"] + ",";
                }
                if (reader.HasRows)
                {
                    if (result != null) result = result.Substring(0, result.LastIndexOf(","));
                }

                reader.Close();
                cmd.Parameters.Clear();
                return result;
            }
            catch (SqlException e)
            {
                return result;
            }
            finally
            {
                con.Close();
            }
        }

        #endregion

        #region Public Methods - Remote Database

        public bool SetConnectionStringForService(String serviceId, String connectionString)
        {
            var sql = new StringBuilder();
            sql.Append("UPDATE APPLICATION_INSTALL_SETTINGS ");
            sql.Append("SET SETTING_VALUE = @connectionString ");
            sql.Append("FROM APPLICATION_INSTALL INNER JOIN ");
            sql.Append("    APPLICATION_INSTALL_MAP ON APPLICATION_INSTALL.INSTALL_ID = APPLICATION_INSTALL_MAP.INSTALL_ID INNER JOIN ");
            sql.Append("    APPLICATION_INSTALL_SETTINGS ON APPLICATION_INSTALL_MAP.INSTALL_MAP_ID = APPLICATION_INSTALL_SETTINGS.INSTALL_MAP_ID ");
            sql.Append("WHERE (APPLICATION_INSTALL.INSTALL_ID = @id) AND (APPLICATION_INSTALL_SETTINGS.SETTING_NAME = 'CMSConnectionString') ");

            try
            {
                SetRemoteDatabaseConnection();
                var cmd = new SqlCommand(sql.ToString(), con);
                cmd.Parameters.Add("@id", SqlDbType.VarChar, serviceId.Length).Value = serviceId;
                cmd.Parameters.Add("@connectionString", SqlDbType.VarChar, connectionString.Length).Value = connectionString;
                con.Open();

                int affectedRows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                if (affectedRows == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        #endregion

        #region GetListOfServicesWithFullInfo

        public List<CmsDocsServiceExtendedModel> GetListOfServicesWithFullInfo()
        {
            //Найти все активные инстансы и получить список уникальных айдих сервисов
            var instances = GetAllActiveServiceInstances();
            var ids = GetDistinctIdsFromInstancesList(instances);
            //По айдихам выбрать все активные сервисы
            //Для каждого сервиса достать все параметры
            var services = GetAllActiveServicesByIds(ids);
            SetInstancesForServices(services, instances);
            SetUsageInformationForServices(services);
            return services;
        }

        private List<CmsDocsServiceExtendedInstanceModel> GetAllActiveServiceInstances()
        {
            var sql = new StringBuilder();
            sql.Append("SELECT * ");
            sql.Append("FROM SERVICE_INSTANCE_INFORMATION (NOLOCK) ");
            sql.Append("WHERE SII_IS_ACTIVE = 1 ");

            try
            {
                SetLocalDatabaseConnection();
                var cmd = new SqlCommand(sql.ToString(), con);
                con.Open();
                var reader = cmd.ExecuteReader();

                var instances = new List<CmsDocsServiceExtendedInstanceModel>();
                while (reader.Read())
                {
                    //reader.GetString(reader.GetOrdinal("LastName"));
                    var instance = new CmsDocsServiceExtendedInstanceModel(Convert.ToInt64(reader["SII_ID"].ToString()),
                        Convert.ToInt64(reader["INSTALL_ID"].ToString()), true, reader["SII_NAME"].ToString(),
                        reader["SII_DESCRIPTION"].ToString(), reader["SII_OWNER_NAME"].ToString(),
                        reader["SII_OWNER_EMAIL"].ToString(), reader["SII_URL"].ToString());
                    instances.Add(instance);
                }

                //C&C
                reader.Close();
                cmd.Parameters.Clear();
                return instances;
            }
            catch (SqlException e)
            {
                throw new ApplicationException(e.Message,e);
            }
            finally
            {
                con.Close();
            }
        }

        private List<CmsDocsServiceExtendedModel> GetAllActiveServicesByIds(List<string> ids)
        {
            if (ids == null || ids.Count == 0) return null;
            var joinedIds = string.Join(",", ids.ToArray());
            var services = new List<CmsDocsServiceExtendedModel>();

            var sql = new StringBuilder();
            sql.Append("SELECT ai.COMPUTER_NAME, ai.INSTALL_ID, ais.SETTING_NAME, ais.SETTING_VALUE ");
            sql.Append("FROM APPLICATION_INSTALL AS ai INNER JOIN ");
            sql.Append("    APPLICATION_INSTALL_MAP AS aim ON ai.INSTALL_ID = aim.INSTALL_ID INNER JOIN ");
            sql.Append("    APPLICATION_INSTALL_SETTINGS AS ais ON aim.INSTALL_MAP_ID = ais.INSTALL_MAP_ID ");
            sql.Append("WHERE (ai.IS_ACTIVE = 1) AND (ai.INSTALL_ID IN (" + joinedIds + ")) ");
            sql.Append("ORDER BY ai.INSTALL_ID ");

            try
            {
                SetRemoteDatabaseConnection();
                var cmd = new SqlCommand(sql.ToString(), con);
                //cmd.Parameters.Add("@ids", SqlDbType.VarChar, joinedIds.Length).Value = joinedIds;
                con.Open();
                var reader = cmd.ExecuteReader();

                GatherServiceParamater(services, reader);

                //C&C
                reader.Close();
                cmd.Parameters.Clear();
            }
            catch (SqlException e)
            {
                throw new ApplicationException(e.Message,e);
            }
            finally
            {
                con.Close();
            }
            return services;
        }

        private void SetUsageInformationForServices(List<CmsDocsServiceExtendedModel> services)
        {
            if (services == null || services.Count == 0) return;
            var sql = new StringBuilder();
            sql.Append("SELECT cdw.CMS_WEBSERVICE_LOCATION_NAME, co.OFFICE_CODE, cdw.CMS_WEBSERVICE_ID ");
            sql.Append("FROM CMS_DOCUMENT_WEBSERVICE AS cdw INNER JOIN ");
            sql.Append("     CMS_DOCUMENT_WEBSERVICE_LOCATION AS cdwl ON cdw.CMS_WEBSERVICE_ID = cdwl.CMS_WEBSERVICE_ID INNER JOIN ");
            sql.Append("     CMS_OFFICE AS co ON cdwl.OFFICE_ID = co.OFFICE_ID ");
            sql.Append("WHERE (cdwl.ALLOW_UPLOAD = 1) AND (cdw.CMS_WEBSERVICE_URL = @url) ");
            sql.Append("ORDER BY cdw.CMS_WEBSERVICE_ID ");

            try
            {
                foreach (var service in services)
                {
                    try
                    {
                        string connectionString;
                        var flag =
                            service.ServiceParameters.TryGetValue(
                                CmsDocsServiceSimpleModel.CmsDocsServiceSetting.CmsConnectionString, out connectionString);
                        if (flag && !String.IsNullOrWhiteSpace(connectionString))
                        {
                            SetConnection(connectionString);

                            foreach (var instance in service.Instances)
                            {
                                var url = instance.Url;

                                var cmd = new SqlCommand(sql.ToString(), con);
                                cmd.Parameters.Add("@url", SqlDbType.VarChar, url.Length).Value = url;
                                con.Open();
                                var reader = cmd.ExecuteReader();

                                GatherAdditionalInstanceInformation(instance, reader);

                                //C&C
                                reader.Close();
                                cmd.Parameters.Clear();
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        //skip this servoce if error occured
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
            catch (SqlException e)
            {
                throw new ApplicationException(e.Message, e);
            }
            finally
            {
                con.Close();
            }
        }

        #endregion

        #endregion

    }
}